package org.bigjava.travel.provider;

import org.springframework.stereotype.Component;

@Component
public class UserSqlProvider {

    //用户登录方法
    public String login() {
        return "SELECT * FROM t_user WHERE USER_NAME = #{userName} AND PASSWORD = #{password} AND DELETE_STATUS=0";
    }

    //所有用户数量
    public String count(){ return "SELECT count(*) FROM t_user WHERE DELETE_STATUS=0"; }

    //通过ID查找用户
    public String findById() {
        return "SELECT * FROM t_user WHERE ID = #{id} AND DELETE_STATUS=0";
    }

    //通过用户名查找用户
    public String findByUserName() {
        return "SELECT * FROM t_user WHERE USER_NAME = #{userName} AND DELETE_STATUS=0";
    }

    //查找所有的用户
    public String findList(){
        return "SELECT * FROM t_user WHERE DELETE_STATUS=0 ORDER BY ID ASC";
    }

    //模糊查询
    public String findListByQuery(){
        return "SELECT * FROM t_user WHERE DELETE_STATUS=0 AND USER_NAME LIKE #{query,jdbcType=VARCHAR} ORDER BY ID ASC";
    }

    //修改用户
    public String update(){
        return "UPDATE t_user SET USER_NAME=#{userName}, " +
                "PASSWORD=#{password},LINK_TEL=#{linkTel},NAME=#{name},IC_CODE=#{icCode},STATE=#{state},PROVINCE=#{province} IMAGE=#{image} WHERE ID=#{id}";
    }

    //保存用户
    public String save(){
        return "INSERT INTO t_user(USER_NAME,PASSWORD,LINK_TEL,NAME,IC_CODE,STATE,PROVINCE,IMAGE) VALUES" +
                "(#{userName},#{password},#{linkTel},#{name},#{icCode},#{state},#{province},#{image})";
    }

    //根据用户ID删除用户
    public String deleteById(){
        return "UPDATE t_user SET DELETE_STATUS=1 WHERE id=#{id}";
    }

    //通过sql语句来绑定省份表和用户表
    public String countPorvice(){
        return "SELECT p.`id` PROVICE,COUNT(u.`PROVINCE`) COUNT FROM t_province p LEFT JOIN t_user u ON u.`PROVINCE` = p.`id` GROUP BY p.id;";
    }

    //修改用户状态
    public String state1count(){
        return "SELECT count(*) FROM t_user WHERE STATE=1 AND DELETE_STATUS=0";
    }

    //修改用户状态
    public String state2count(){
        return "SELECT count(*) FROM t_user WHERE STATE=2  AND DELETE_STATUS=0";
    }
}
